package com.qdy.service.impl;

import com.qdy.entity.Course;
import com.qdy.repository.CourseRepository;
import com.qdy.service.CourseService;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;


import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.*;
import javax.persistence.Query;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by qudingyi on 2017/4/26.
 */
@Service
public class CourseServiceImpl implements CourseService {
    private static  final String BASE_COURSE_VIEW="select \n" +
            "uc.id,\n" +
            "uc.title,\n" +
            "uc.type,\n" +
            "uc.user_id as userId,\n" +
            "uc.venue_id as venueId,\n" +
            "uc.address as address,\n" +
            "uc.apply_number as applyNumber,\n" +
            "uc.coach,\n" +
            "uc.create_time as createTime,\n" +
            "uc.details,\n" +
            "uc.end_time as endTime,\n" +
            "uc.start_time as startTime,\n" +
            "uc.geohash,\n" +
            "uc.imgs,\n" +
            "uc.price,\n" +
            "uc.day_setting as daySetting,\n" +
            "u.acatar,u.nick_name as nickName ,u.gender as gender, \n" +
            "COUNT(ucm.id) as historyEnrollNumber \n" +
            "from t_course uc\n" +
            "LEFT JOIN t_course_membear ucm on ucm.course_id=uc.id \n" +
            "LEFT JOIN t_user u on u.id=uc.user_id\n" +
            "GROUP BY uc.id ORDER BY uc.create_time";


    @Autowired
    private CourseRepository courseRepository;
    @PersistenceUnit
    private EntityManagerFactory emf;
    @Override
    public Map<String,Object> getUserCourseInfo(Long couseId, Date enrollTime) {
        EntityManager entityManager = emf.createEntityManager();
        Query query =entityManager.createNativeQuery("SELECT v.* ,COUNT(cum.id) as enrollNumber " +
                "FROM("+BASE_COURSE_VIEW+")  as v LEFT JOIN t_course_membear cum on cum.course_id=v.id  and cum.enroll_time=:enrollTime \n" +
                "where \n" +
                "v.daySetting like(:day)\n" +
                "and v.id=:couseId \n"+
                "GROUP BY v.id ;");
        int day=enrollTime.getDay();
        day=day==0?7:day;
        query.setParameter("enrollTime",enrollTime,TemporalType.DATE)
                .setParameter("day","%"+day+"%")
                .setParameter("couseId",couseId);
        query.unwrap(org.hibernate.SQLQuery.class)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        Map<String,Object> data=(Map<String,Object>)query.getSingleResult();
        return data;
    }

    @Override
    public List<Map<String, Object>> listCourseByUserAll(Date enrollTime) {

        EntityManager entityManager = emf.createEntityManager();
        Query query =entityManager.createNativeQuery("SELECT v.* ,COUNT(cum.id) as enrollNumber " +
                "FROM("+BASE_COURSE_VIEW+")  as v LEFT JOIN t_course_membear cum on cum.course_id=v.id  and cum.enroll_time=:enrollTime \n" +
                "where  \n" +
                "v.type=2 \n"+
                "and v.daySetting like(:day)\n" +
                "GROUP BY v.id ;");
        int day=enrollTime.getDay();
        day=day==0?7:day;
        query.setParameter("enrollTime",enrollTime,TemporalType.DATE)
                .setParameter("day","%"+day+"%");

        query.unwrap(org.hibernate.SQLQuery.class)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String,Object>> datas=query.getResultList();
        entityManager.close();
        return datas;
    }

    @Override
    public List<Map<String, Object>> listCourseByVenueAll(Date enrollTime) {
        EntityManager entityManager = emf.createEntityManager();
        Query query =entityManager.createNativeQuery("SELECT v.* ,COUNT(cum.id) as enrollNumber " +
                "FROM("+BASE_COURSE_VIEW+")  as v LEFT JOIN t_course_membear cum on cum.course_id=v.id and cum.enroll_time=:enrollTime \n" +
                "where \n" +
                "v.type=1 \n"+
                "and v.daySetting like(:day)\n" +
                "GROUP BY v.id ;");
        int day=enrollTime.getDay();
        day=day==0?7:day;
        query.setParameter("enrollTime",enrollTime,TemporalType.DATE)
                .setParameter("day","%"+day+"%");

        query.unwrap(org.hibernate.SQLQuery.class)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String,Object>> datas=query.getResultList();
        entityManager.close();
        return datas;
    }

    @Override
    public List<Map<String, Object>> listCourseByUserId(Long userId) {
        EntityManager entityManager = emf.createEntityManager();
        Query query =entityManager.createNativeQuery("SELECT v.*" +
                "FROM("+BASE_COURSE_VIEW+")  as v \n" +
                "where v.userId=:userId ");
        query.setParameter("userId",userId);
        query.unwrap(org.hibernate.SQLQuery.class)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> data=query.getResultList();
        entityManager.close();
        return  data;
    }

    @Override
    public List<Map<String, Object>> listCourseByVenueId(Long venueId) {
        EntityManager entityManager = emf.createEntityManager();
        Query query =entityManager.createNativeQuery("SELECT v.*" +
                "FROM("+BASE_COURSE_VIEW+")  as v \n" +
                "where v.venueId=:venueId ");
        query.setParameter("venueId",venueId);
        query.unwrap(org.hibernate.SQLQuery.class)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> data=query.getResultList();
        entityManager.close();
        return  data;
    }

    @Override
    public void save(Course course) {
        EntityManager  entityManager = emf.createEntityManager();
        entityManager.getTransaction().begin();
        entityManager.persist(course);
        entityManager.getTransaction().commit();
        entityManager.close();

    }


}
